package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * 
 * @author shefali
 * In all the functions of this class a connection to the database is achieved via a DriverManager class
 * Once the JDBC drivers are loaded, getConnection method is applied to the Driver Manager class using the
 * Appropriate url and user name, password
 * 
 * Prepared statement is used to send SQL statements to the database server since it is more efficient than the
 * Statement object
 * The ResultSet class is used to access the table of data generated by executing the sql queries
 *
 */

public class WorkshopDataAccess1 {
	/**
	 * 
	 * @param conferenceId
	 * @param sDate
	 * @return List containing workshop Details
	 * @throws SQLException
	 * @throws IOException
	 * 
	 * Selects the  workshop details of the workshops to be held on  a given date related to a particular conference.
	 */
	
	public List<Workshop> getWorkshopsForSelectedConference(int conferenceId, String sDate) throws SQLException, IOException
{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset;
		
		List<Workshop> workshopDetails = new ArrayList<Workshop>();
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			
			String query0 = "Select wtitle, description, levelofexperience, startTime, endTime, speakerId from workshop where conferenceId = ? and heldOn = ? and booked = ?";
			
			pstmt = conn.prepareStatement(query0);
			
			pstmt.setInt(1,conferenceId);
			pstmt.setString(2,sDate);
			pstmt.setInt(3, 1);
			rset = pstmt.executeQuery();
			while(rset.next())
			{
				Workshop w = new Workshop();
				w.setWorkshopTitle(rset.getString(1));
				w.setWorkshopDescr(rset.getString(2));
				w.setLevelOfExperience(rset.getString(3));
				w.setStartTime(rset.getString(4));
				w.setEndTime(rset.getString(5));
				w.setSpeakerId(rset.getInt(6));
				workshopDetails.add(w);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return workshopDetails;
}
	/**
	 * 
	 * @param speakerId
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * Gets speaker info about the 
	 */

	public List<Speaker>getSpeakerInfo(int speakerId) throws SQLException, IOException
	
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset;
		
		List<Speaker> speakerDetails = new ArrayList<Speaker>();
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			
			String query0 = "Select fname, lname from speaker where speakerId = ?";
			pstmt = conn.prepareStatement(query0);
			pstmt.setInt(1, speakerId);
			rset = pstmt.executeQuery();
			
			while(rset.next())
			{
				Speaker s = new Speaker();
				s.setFname(rset.getString(1));
				s.setLname(rset.getString(2));
				speakerDetails.add(s);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
			
		return speakerDetails;
		
	}


	/**
	 * 
	 * @param confId
	 * @param wTitle
	 * @param wDescription
	 * @param exp
	 * @param startTime
	 * @param endTime
	 * @param toBeHeldOn
	 * @param email
	 * @throws SQLException
	 * @throws IOException
	 * Saves the details related to a particular workshop. Every workshop has a booked column  which is set to 1 when the workshop has been
	 * allocated a time spot in the conference on a particular day
	 * 
	 */
	public void saveWorkshopDetails(int confId,String wTitle,String wDescription,String exp,String startTime, String endTime,String toBeHeldOn,String email) throws SQLException, IOException 
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset;
		int speakerId = 0;
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			
			String query0 = "Select speakerId from speaker where email = ?";
			pstmt = conn.prepareStatement(query0);
			pstmt.setString(1, email);
			rset = pstmt.executeQuery();
			while(rset.next())
			{
				speakerId = rset.getInt(1);
			}
			
			String query1 = " Update workshop set wTitle = ?, description = ?, levelofexperience = ?, speakerId = ?, booked = ? where conferenceId = ? and startTime = ? and endTime = ? and heldOn =?";
			pstmt = conn.prepareStatement(query1);
			pstmt.setString(1,wTitle);
			pstmt.setString(2,wDescription);
			pstmt.setString(3,exp);
			pstmt.setInt(4, speakerId);
			pstmt.setInt(5,1);
			pstmt.setInt(6,confId);
			pstmt.setString(7,startTime);
			pstmt.setString(8,endTime);
			pstmt.setString(9,toBeHeldOn);
			
			pstmt.executeUpdate();
			
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		
		
		
	}
	
	/**
	 * 
	 * @param conferenceId
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * Every conference is a 2-day event with each day divided into 5 time slots from morning till evening during which time the
	 * workshops will be organized. Once a time slot is assigned to a particular workshop the booked bit is set to 1 and only those
	 * time slots for which the booked bit is 0 that means the time slot is still open are returned by the function along with the date the
	 * time slots are available on
	 */
	
	public List<Workshop> getAvailableTimeSlots(int conferenceId)throws SQLException, IOException 
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset;
		List<Workshop> timeslots = new ArrayList<Workshop>();
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			
			String query = "Select startTime, endTime, heldOn from workshop where conferenceId = ? and booked = ? order by heldOn,startTime ASC";
			
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, conferenceId);
			pstmt.setInt(2,0);
			
			rset = pstmt.executeQuery();
			
			while(rset.next())
			{
				Workshop w = new Workshop();
				w.setStartTime(rset.getString(1));
				w.setEndTime(rset.getString(2));
				w.setHeldOn(rset.getString(3));
				timeslots.add(w);
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		
		
		return timeslots;
	}

}
